package di.ass.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

	// database version
	private static final int DATABASE_VERSION = 3;
	// database name
	private static final String DATABASE_NAME = "ShoppingList";
	// formula table name
	private static final String TABLE_FORMULA = "Formulas";
	// formula table colume name
	// use foreign key
	private static final String KEY_FORMULA_ID = "formula_id";
	private static final String KEY_FORMULA_NAME = "formula_name";
	private static final String KEY_FORMULA_DATE = "formula_date";
	// food table name
	private static final String TABLE_FOOD = "Foods";
	// food table colume name
	private static final String KEY_FOOD_ID = "food_id";
	private static final String KEY_FOOD_NAME = "food_name";
	private static final String KEY_FOOD_QUANTITY = "quantity";
	private static final String KEY_FOOD_UNITARY = "unitary";
	private static final String KEY_FOOD_CHECKED = "food_checked";

	private static final String KEY_VIEWFOODS = "ViewFoods";

	private static final String TABLE_LANGUAGE = "language";
	private static final String KEY_NAME_LANG = "name";
	private static final String KEY_LANG_CHECK = "used";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public synchronized void close() {
		// TODO Auto-generated method stub
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String CREATE_FORMULA_TABLE = "CREATE TABLE " + TABLE_FORMULA + "("
				+ KEY_FORMULA_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ KEY_FORMULA_NAME + " TEXT NOT NULL," + KEY_FORMULA_DATE
				+ " DATE) ";
		db.execSQL(CREATE_FORMULA_TABLE);
		String CREATE_FOOD_TABLE = "CREATE TABLE " + TABLE_FOOD + "("
				+ KEY_FOOD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ KEY_FORMULA_ID + " INTEGER NOT NULL, " + KEY_FOOD_NAME
				+ " TEXT NOT NULL, " + KEY_FOOD_QUANTITY + " TEXT, "
				+ KEY_FOOD_UNITARY + " TEXT, " + KEY_FOOD_CHECKED
				+ " BOOLEAN, " + "FOREIGN KEY(" + KEY_FORMULA_ID
				+ ") REFERENCES " + TABLE_FORMULA + "(" + KEY_FORMULA_ID + ")"
				+ ")";
		db.execSQL(CREATE_FOOD_TABLE);

		String CREATE_TRIGGER = "CREATE TRIGGER fk_foodformula_formulaid "
				+ " BEFORE INSERT " + " ON " + TABLE_FOOD
				+ " FOR EACH ROW BEGIN" + " SELECT CASE WHEN ((SELECT "
				+ KEY_FORMULA_ID + " FROM " + TABLE_FORMULA + " WHERE "
				+ KEY_FORMULA_ID + "=new." + KEY_FORMULA_ID + " ) IS NULL)"
				+ " THEN RAISE (ABORT, 'Foreign Key Violation') END;" + " END;";
		db.execSQL(CREATE_TRIGGER);
		String CREATE_VIEW = "CREATE VIEW " + KEY_VIEWFOODS + " AS SELECT "
				+ TABLE_FOOD + "." + KEY_FOOD_ID + "," + " " + TABLE_FOOD + "."
				+ KEY_FOOD_NAME + "," + " " + TABLE_FOOD + "."
				+ KEY_FOOD_QUANTITY + "," + " " + TABLE_FOOD + "."
				+ KEY_FOOD_UNITARY + "," + " " + TABLE_FOOD + "."
				+ KEY_FOOD_CHECKED + "," + " " + TABLE_FORMULA + "."
				+ KEY_FORMULA_ID + "" + " FROM " + TABLE_FOOD + " JOIN "
				+ TABLE_FORMULA + " ON " + TABLE_FOOD + "." + KEY_FORMULA_ID
				+ " =" + TABLE_FORMULA + "." + KEY_FORMULA_ID;
		db.execSQL(CREATE_VIEW);
		String CREATE_LANG_TABLE = "CREATE TABLE " + TABLE_LANGUAGE + "("
				+ KEY_NAME_LANG + " TEXT, " + KEY_LANG_CHECK + " BOOLEAN)";
		db.execSQL(CREATE_LANG_TABLE);

	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		super.onOpen(db);
		if (!db.isReadOnly()) {
			db.execSQL("PRAGMA foreign_keys=ON;");
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FORMULA);
		db.execSQL("DROP TABLE IF EXTSTS " + TABLE_FOOD);
		db.execSQL("DROP TABLE IF EXTSTS fk_foodformula_formulaid");
		db.execSQL("DROP TABLE IF EXTSTS " + KEY_VIEWFOODS);
		db.execSQL("DROP TABLE IF EXTSTS " + TABLE_LANGUAGE);
		onCreate(db);
	}

	// Check Formula added. If Formula added return true, else return false
	public int checkFormulaDup(final Formula formula) {
		SQLiteDatabase db = this.getReadableDatabase();
		String strQuery = "SELECT "+KEY_FORMULA_ID+" FROM " + TABLE_FORMULA + " WHERE "
				+ KEY_FORMULA_NAME + "='" + formula.getName() + "' AND "
				+ KEY_FORMULA_DATE + "='" + formula.getDate() + "'";
		Cursor cursor = db.rawQuery(strQuery, null);
		int count = cursor.getCount();
		if (count == 0) {
			cursor.close();
			db.close();
			return -1;
		} else {
			int id_return = -1;
			if (cursor.moveToFirst())
				id_return = cursor.getInt(0);
			cursor.close();
			db.close();
			return id_return;
		}
	}

	// Add new formula
	public boolean addFormula(Formula formula) {

		if (checkFormulaDup(formula) == -1) {
			SQLiteDatabase db = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(KEY_FORMULA_NAME, formula.getName());
			values.put(KEY_FORMULA_DATE, formula.getDate());
			long isSuccess = db.insert(TABLE_FORMULA, null, values);
			db.close();
			if (isSuccess==-1)
				return false;
			return true;
		} else {
			return false;
		}
	}

	// Check Formula added. If Formula added return true, else return false
	public int checkFoodDup(final Food food) {
		SQLiteDatabase db = this.getReadableDatabase();
		String strQuery = "SELECT "+KEY_FOOD_ID+" FROM " + TABLE_FOOD + " WHERE "
				+ KEY_FOOD_NAME + "='" + food.getName() + "' AND "
				+ KEY_FORMULA_ID + "=" + food.getId_formula();
		Cursor cursor = db.rawQuery(strQuery, null);
		int count = cursor.getCount();
		if (count == 0) {
			cursor.close();
			db.close();
			return -1;
		} else {
			int id_return = -1;
			if (cursor.moveToFirst())
				id_return = cursor.getInt(0);
			cursor.close();
			db.close();
			return id_return;
		}
	}

	// Add new food
	public boolean addFood(Food food) {
		if (checkFoodDup(food) == -1) {
			SQLiteDatabase db = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(KEY_FOOD_NAME, food.getName());
			values.put(KEY_FOOD_QUANTITY, food.getQuantity());
			values.put(KEY_FORMULA_ID, food.getId_formula());
			values.put(KEY_FOOD_CHECKED, food.getBought());
			values.put(KEY_FOOD_UNITARY, food.getUnitary());
			long isSuccess = db.insert(TABLE_FOOD, null, values);
			db.close();
			if (isSuccess ==-1)
				return false;
			return true;
		} else {
			return false;
		}
	}

	// Get id formual
	public int getIdFormula(Formula formula) {
		SQLiteDatabase db = this.getReadableDatabase();
		String strQuery = "SELECT "+KEY_FORMULA_ID+" FROM " + TABLE_FORMULA + " WHERE "
				+ KEY_FORMULA_NAME + "='" + formula.getName() + "' AND "
				+ KEY_FORMULA_DATE + "='" + formula.getDate() + "'";
		Cursor cursor = db.rawQuery(strQuery, null);
		int id = -1;
		if (cursor.moveToFirst())
			id = cursor.getInt(0);
		cursor.close();
		db.close();
		return id;
	}

	// Get max id formula
	public int getMaxIdFormula() {
		SQLiteDatabase db = this.getReadableDatabase();
		String strQuery = "SELECT MAX(" + KEY_FORMULA_ID + ") FROM "
				+ TABLE_FORMULA;
		Cursor cursor = db.rawQuery(strQuery, null);
		int maxId = 0;
		if (cursor.getCount() > 0)
			if (cursor.moveToFirst())
				maxId = cursor.getInt(0);
		cursor.close();
		db.close();
		return maxId;
	}

	// Get bought food
	public int getBoughtFood(Food food) {
		SQLiteDatabase db = this.getReadableDatabase();
		String strQuery = "SELECT " + KEY_FOOD_CHECKED + " FROM " + TABLE_FOOD
				+ " WHERE " + KEY_FOOD_ID + "=" + food.getId();
		Cursor cursor = db.rawQuery(strQuery, null);
		int bought = -1;
		if (cursor.moveToFirst())
			bought = cursor.getInt(0);
		cursor.close();
		db.close();
		return bought;
	}

	// getting signle Formula
	public Formula getFormula(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		String strQuery = "SELECT "+KEY_FORMULA_ID+","+KEY_FORMULA_NAME+","+KEY_FORMULA_DATE+" FROM " + TABLE_FORMULA + " WHERE " + id
				+ "=" + KEY_FORMULA_ID;
		Cursor cursor = db.rawQuery(strQuery, null);

		if (cursor != null)
			cursor.moveToFirst();
		Formula formula = new Formula(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), cursor.getString(2));
		db.close();
		return formula;
	}

	// getting all Formula with statistic = 0(ALL) | 1 (Done) | -1 (Not Done)
	public Formula[] getAllFormulas(int statistic, String nameFormula ) {
		String selectQuery;
		selectQuery = "SELECT "+KEY_FORMULA_ID+","+KEY_FORMULA_NAME+","+KEY_FORMULA_DATE+
				" FROM " + TABLE_FORMULA +" WHERE "+KEY_FORMULA_NAME+" LIKE '%"+nameFormula+"%' ORDER BY 3 DESC";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		int sumFormulas = cursor.getCount();
		Formula[] formula;
		if (sumFormulas > 0) {
			formula = new Formula[sumFormulas];
			int i = 0;
			if (cursor.moveToFirst()) {
				do {
					formula[i] = new Formula();
					formula[i].setId(cursor.getInt(0));
					formula[i].setName(cursor.getString(1));
					formula[i].setDate(cursor.getString(2));
					i++;
				} while (cursor.moveToNext());
			}
			cursor.close();
			db.close();
		} else {
			cursor.close();
			db.close();
			return null;
		}
		String query = null;
		switch (statistic) {
		case 0:
			return formula;

		case 1:
			Cursor cursor1 = null;
			db = this.getReadableDatabase();
			int j = 0,
			count = 0;
			for (Formula i : formula) {
				query = "SELECT COUNT(*), SUM(" + KEY_FOOD_CHECKED + ") FROM "
						+ TABLE_FOOD + " WHERE " + KEY_FORMULA_ID + "="
						+ i.getId();
				cursor1 = db.rawQuery(query, null);
				if (cursor1.moveToFirst())
					if (cursor1.getInt(0) == cursor1.getInt(1)
							&& cursor1.getInt(0) > 0)
						count++;
			}
			j = 0;
			if (count > 0) {
				Formula[] formula1 = new Formula[count];
				for (Formula i : formula) {

					query = "SELECT COUNT(*), SUM(" + KEY_FOOD_CHECKED
							+ ") FROM " + TABLE_FOOD + " WHERE "
							+ KEY_FORMULA_ID + "=" + i.getId();
					cursor1 = db.rawQuery(query, null);
					if (cursor1.moveToFirst())
						if (cursor1.getInt(0) == cursor1.getInt(1)
								&& cursor1.getInt(0) > 0)
							formula1[j++] = new Formula(i);

				}
				cursor1.close();
				db.close();
				return formula1;
			} else {
				cursor1.close();
				db.close();
				return null;
			}

		case -1:
			db = this.getReadableDatabase();
			Cursor cursor2 = null;
			int k = 0,
			count2 = 0;
			for (Formula i : formula) {
				query = "SELECT COUNT(*), SUM(" + KEY_FOOD_CHECKED + ") FROM "
						+ TABLE_FOOD + " WHERE " + KEY_FORMULA_ID + "="
						+ i.getId();
				cursor2 = db.rawQuery(query, null);
				if (cursor2.moveToFirst())
					if (cursor2.getInt(0) > cursor2.getInt(1)
							|| cursor2.getInt(0) == 0) {
						count2++;

					}
			}
			if (count2 > 0) {
				Formula[] formula2 = new Formula[count2];
				for (Formula i : formula) {

					query = "SELECT COUNT(*), SUM(" + KEY_FOOD_CHECKED
							+ ") FROM " + TABLE_FOOD + " WHERE "
							+ KEY_FORMULA_ID + "=" + i.getId();
					cursor2 = db.rawQuery(query, null);
					if (cursor2.moveToFirst())
						if (cursor2.getInt(0) > cursor2.getInt(1)
								|| cursor2.getInt(0) == 0)
							formula2[k++] = new Formula(i);

				}
				cursor2.close();
				db.close();
				return formula2;
			} else {
				cursor2.close();
				db.close();
				return null;
			}

		default:
			return formula;
		}
	}

	// getting signle food
	public Food getOneFood(int food_id) {
		SQLiteDatabase db = this.getReadableDatabase();
		Food food = new Food();
		String selectStr = "SELECT "+KEY_FOOD_ID+","+KEY_FORMULA_ID+","+KEY_FOOD_NAME+","+KEY_FOOD_QUANTITY+
				","+KEY_FOOD_UNITARY+","+KEY_FOOD_CHECKED+" FROM " + TABLE_FOOD + " WHERE "
				+ KEY_FOOD_ID + "=" + food_id;
		Cursor cursor = db.rawQuery(selectStr, null);

		if (cursor.moveToFirst()) {
			food.setId(cursor.getInt(0));
			food.setId_formula(cursor.getInt(1));
			food.setName(cursor.getString(2));
			food.setQuantity(cursor.getString(3));
			food.setUnitary(cursor.getString(4));
			food.setChecked(Boolean.parseBoolean(cursor.getString(5)));
			cursor.close();
			db.close();
			return food;
		} else {
			cursor.close();
			db.close();
			return null;
		}
	}

	// get all foods by id formula, statistic =0(ALL)|1(DOne)|-1(Not done)
	public Food[] getFood(int formula_id, int statistic) {
		SQLiteDatabase db = this.getReadableDatabase();
		String selectStr = null;
		switch (statistic) {
		case 0:
			selectStr = "SELECT "+KEY_FOOD_ID+","+KEY_FORMULA_ID+","+KEY_FOOD_NAME+","+KEY_FOOD_QUANTITY+
				","+KEY_FOOD_UNITARY+","+KEY_FOOD_CHECKED+" FROM " + TABLE_FOOD + " WHERE "
					+ KEY_FORMULA_ID + "=" + formula_id + " ORDER BY 3 ASC";

			break;
		case 1:
			selectStr = "SELECT "+KEY_FOOD_ID+","+KEY_FORMULA_ID+","+KEY_FOOD_NAME+","+KEY_FOOD_QUANTITY+
				","+KEY_FOOD_UNITARY+","+KEY_FOOD_CHECKED+" FROM " + TABLE_FOOD + " WHERE "
					+ KEY_FORMULA_ID + "=" + formula_id + " AND "
					+ KEY_FOOD_CHECKED + "=1" + " ORDER BY 3 ASC";

			break;
		case -1:
			selectStr = "SELECT "+KEY_FOOD_ID+","+KEY_FORMULA_ID+","+KEY_FOOD_NAME+","+KEY_FOOD_QUANTITY+
				","+KEY_FOOD_UNITARY+","+KEY_FOOD_CHECKED+" FROM " + TABLE_FOOD + " WHERE "
					+ KEY_FORMULA_ID + "=" + formula_id + " AND "
					+ KEY_FOOD_CHECKED + "=0" + " ORDER BY 3 ASC";
			break;
		default:
			selectStr = "SELECT "+KEY_FOOD_ID+","+KEY_FORMULA_ID+","+KEY_FOOD_NAME+","+KEY_FOOD_QUANTITY+
				","+KEY_FOOD_UNITARY+","+KEY_FOOD_CHECKED+" FROM " + TABLE_FOOD + " WHERE "
					+ KEY_FORMULA_ID + "=" + formula_id + " ORDER BY 3 ASC";

			break;
		}
		Cursor cursor = db.rawQuery(selectStr, null);
		int sumFoods = cursor.getCount();
		if (sumFoods > 0) {
			Food[] food = new Food[sumFoods];
			int i = 0;
			if (cursor.moveToFirst()) {
				do {
					food[i] = new Food();
					food[i].setId(cursor.getInt(0));
					food[i].setId_formula(cursor.getInt(1));
					food[i].setName(cursor.getString(2));
					food[i].setQuantity(cursor.getString(3));
					food[i].setUnitary(cursor.getString(4));
					food[i].setChecked(Boolean.parseBoolean(cursor.getString(5)));
					i++;
				} while (cursor.moveToNext());
			}
			cursor.close();
			db.close();
			return food;
		} else {
			cursor.close();
			db.close();
			return null;
		}
	}

	// Updating signle Formula
	public boolean updateFormula(Formula formula) {
		if (checkFormulaDup(formula) == -1) {
			SQLiteDatabase db = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(KEY_FORMULA_NAME, formula.getName());
			values.put(KEY_FORMULA_DATE, formula.getDate());
			db.update(TABLE_FORMULA, values,
					KEY_FORMULA_ID + " = " + formula.getId(), null);
			db.close();
			return true;
		} else {
			if (checkFormulaDup(formula) == formula.getId())
				return true;
			else
				return false;
		}
	}

	// Updating signle Food
	public boolean updateFood(Food food) {
		if (checkFoodDup(food) == -1 || checkFoodDup(food) == food.getId()) {
			SQLiteDatabase db = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(KEY_FOOD_NAME, food.getName());
			values.put(KEY_FOOD_QUANTITY, food.getQuantity());
			values.put(KEY_FOOD_CHECKED, food.getBought());
			values.put(KEY_FOOD_UNITARY, food.getUnitary());
			db.update(TABLE_FOOD, values, KEY_FOOD_ID + "=?",
					new String[] { String.valueOf(food.getId()) });
			db.close();
			return true;
		} else {
			return false;
		}
	}

	// Deleting single Formula
	public void deleteFormula(Formula formula) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_FOOD, KEY_FORMULA_ID + "=?",
				new String[] { String.valueOf(formula.getId()) });

		db.delete(TABLE_FORMULA, KEY_FORMULA_ID + "=?",
				new String[] { String.valueOf(formula.getId()) });
		db.close();

	}

	// Deleting single Food
	public void deleteFood(Food food) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_FOOD, KEY_FOOD_ID + "=?",
				new String[] { String.valueOf(food.getId()) });
		db.close();
	}

	// Getting Formula count
	public int getFormulasCount() {
		String countString = "SELECT * FROM " + TABLE_FORMULA;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countString, null);
		int count = cursor.getCount();
		cursor.close();
		db.close();
		return count;
	}

	// Getting Food count
	public int getFoodsCount() {
		String countString = "SELECT * FROM " + TABLE_FOOD;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countString, null);
		int count = cursor.getCount();
		cursor.close();
		db.close();
		return count;
	}

	// Getting Food count with formula id
	public int getFoodsCount(int formula_id, int bought) {
		String countString = "SELECT * FROM " + TABLE_FOOD + " WHERE "
				+ KEY_FORMULA_ID + "=" + formula_id + " AND "
				+ KEY_FOOD_CHECKED + "=" + bought;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countString, null);
		int count = cursor.getCount();
		cursor.close();
		db.close();
		return count;
	}

	public void addLanguage(String lang, int check) {
		SQLiteDatabase db = this.getWritableDatabase();
		String queryStr = "SELECT * FROM " + TABLE_LANGUAGE + " WHERE "
				+ KEY_NAME_LANG + "='" + lang + "'";
		Cursor cursor = db.rawQuery(queryStr, null);
		if (cursor.getCount() > 0) {
			cursor.close();
			db.close();
			return;
		} else {
			ContentValues values = new ContentValues();
			values.put(KEY_NAME_LANG, lang);
			values.put(KEY_LANG_CHECK, String.valueOf(check));
			db.insert(TABLE_LANGUAGE, null, values);
			cursor.close();
			db.close();
		}
	}

	public String getLanguageUse() {
		String queryStr = "SELECT " + KEY_NAME_LANG + " FROM " + TABLE_LANGUAGE
				+ " WHERE " + KEY_LANG_CHECK + "=1";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(queryStr, null);
		String lang = null;
		if (cursor.moveToFirst())
			lang = cursor.getString(0);
		cursor.close();
		db.close();
		return lang;
	}

	public void updateLang(String lang) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_LANG_CHECK, 1);
		db.update(TABLE_LANGUAGE, values, KEY_NAME_LANG + "=?",
				new String[] { lang });

		values.put(KEY_LANG_CHECK, 0);
		db.update(TABLE_LANGUAGE, values, KEY_NAME_LANG + "!=?",
				new String[] { lang });
		db.close();
	}
}
